import random
import pymysql

from Base.GetYamlFile import getYamlPath

SqlConfig=getYamlPath("SqlConfig")
print(SqlConfig)
deviceCode =  'HF' + str(random.randint(88000001,88003001))
createTime = random.randint(1588922447,1594192847)
status = random.randint(0,5)
print(deviceCode,createTime,status)
list1 = ['1','2','3','4','5','6','7','A8','B8','C8','A9','B9','C9','A10','B10','C10','A11','B11','C11','11','A12','B12','C12','12']
sensor_code = random.choice(list1)

conn = pymysql.connect(**SqlConfig)
cur = conn.cursor()
#根据设备CODE查询楼栋ID，楼层ID
selectBuildingAndFloorSQL ='''
    select building_id,floor_id,id,name from device where device.`code` = %s
'''
#根据楼栋ID查询楼栋名称
selectBuildingNameSQL = '''
    select name from  building where id = %s
'''
selectFloorNameSQL = '''
    select name from floor where id = %s
'''
selectSensorSQL = '''
    SELECT id,sensor.`name` FROM sensor WHERE sensor.`code`=%(code)s AND device_id=%(device_id)s
'''
insertSQL = "INSERT INTO fault (type,enterprise_id,enterprise_name,department_id,department_name,department_man,department_phone,is_outdoor,geographic_id,geographic_name,position,building_id,building_name,building_man,building_phone,floor_id,floor_name,photo_id,pos_x,pos_y,device_id,device_code,device_name,device_type,device_model,sensor_id,sensor_name,site_name,detail,count,`status`) VALUES	(5,4,'自运营企业',2,'测试','tpson003','13666666666',0,330108001,'浙江 杭州市 滨江区 西兴街道','王道公园111',2,'大厦5323','tpson003','13666666666',1,'一层','adfsaashoi','0.4','0.6',2096,'OC12345679','OC12345679',13,208,2391,'OC12345679_剩余电流传感器','OC12345679_剩余电流传感器','OC12345679_剩余电流传感器发生故障',1,0)"

cur.execute(selectBuildingAndFloorSQL,deviceCode)       #传递设备CODE
build = cur.fetchall()
building_id = build[0][0]       #获取楼栋ID
floor_id = build[0][1]      #获取楼层ID
device_id = build[0][2]     #获取设备ID
device_name = build[0][3]       #获取设备NAME
print(building_id,floor_id,device_id,device_name)
cur.execute(selectBuildingNameSQL,building_id)      #传递楼栋ID
buildingName = cur.fetchall()
building_name = buildingName[0][0]      #获取楼栋NAME
print(building_name)
cur.execute(selectFloorNameSQL,floor_id)        #传递楼层ID
floorName = cur.fetchall()
floor_name = floorName[0][0]        #获取楼层NAME
print(floor_name)


values ={
    "device_id":device_id,
    "code":sensor_code
}
print(values)
cur.execute(selectSensorSQL,values)
sensor = cur.fetchall()
sensor_id = sensor[0][0]
sensor_name = sensor[0][1]
print(sensor_id,sensor_name)


# cur.execute(selectSQL,deviceCode)






